package com.jichangxiu.common.utils;

import cn.hutool.core.exceptions.UtilException;
import cn.hutool.core.util.StrUtil;
import com.jichangxiu.common.entity.bo.SecurityRole;
import com.jichangxiu.common.entity.bo.SecurityUser;
import org.apache.commons.lang3.StringUtils;

import java.util.ArrayList;
import java.util.List;

/**
 * SqlUtils:
 *
 * @author Jcx
 * @create 2023-04-03 21:59
 */
public class SqlUtils {

    /**
     * 定义常用的 sql关键字
     */
    public static final String SQL_REGEX = "select |insert |delete |update |drop |count |exec |chr |mid |master |truncate |char |and |declare ";

    /**
     * 仅支持字母、数字、下划线、空格、逗号、小数点（支持多个字段排序）
     */
    public static final String SQL_PATTERN = "[a-zA-Z0-9_\\ \\,\\.]+";

    /**
     * 检查字符，防止注入绕过
     *
     * @param value 字符
     * @return 检查过的字符
     */
    public static String escapeOrderBySql(String value) {
        if (StrUtil.isNotEmpty(value) && !isValidOrderBySql(value)) {
            throw new UtilException("参数不符合规范，不能进行查询");
        }
        return value;
    }

    /**
     * 验证 order by 语法是否符合规范
     *
     * @param value sql 语句
     * @return 是否
     */
    public static boolean isValidOrderBySql(String value) {
        return value.matches(SQL_PATTERN);
    }

    /**
     * SQL关键字检查
     *
     * @param value sql 语句
     */
    public static void filterKeyword(String value) {
        if (StrUtil.isEmpty(value)) {
            return;
        }
        List<String> sqlKeywords = StrUtil.split(SQL_REGEX, "\\|");
        for (String sqlKeyword : sqlKeywords) {
            if (StringUtils.indexOfIgnoreCase(value, sqlKeyword) > -1) {
                throw new UtilException("参数存在SQL注入风险");
            }
        }
    }

    /**
     * 全部数据权限
     */
    public static final String DATA_ALL_SCOPE = "1";

    /**
     * 自定数据权限
     */
    public static final String DATA_CUSTOM_SCOPE = "2";

    /**
     * 部门数据权限
     */
    public static final String DATA_DEPT_SCOPE = "3";

    /**
     * 部门及以下数据权限
     */
    public static final String DATA_DEPT_AND_CHILD_SCOPE = "4";

    /**
     * 仅本人数据权限
     */
    public static final String DATA_SELF_SCOPE = "5";

    /**
     * 获取数据权限
     *
     * @param securityUser 登录用户
     * @param deptAlias    部门别名
     * @param userAlias    用户别名
     * @return 数据权限 sql
     */
    public static String getDataScope(SecurityUser securityUser, String deptAlias, String userAlias) {
        StringBuilder SqlBuilder = new StringBuilder();
        List<String> conditions = new ArrayList<>();
        for (SecurityRole securityRole : securityUser.getSecurityRoles()) {
            String dataScope = securityRole.getDataScope();
            if (conditions.contains(dataScope)) {
                continue;
            }
            if (DATA_ALL_SCOPE.equals(dataScope)) {
                SqlBuilder = new StringBuilder();
                break;
            } else if (DATA_CUSTOM_SCOPE.equals(dataScope)) {
                SqlBuilder.append(StrUtil.format(" OR {}.dept_id IN ( SELECT dept_id FROM platform_dept_role WHERE deleted != '1' AND tenant_id = '{}' AND role_id = '{}' ) ", deptAlias, securityUser.getTenantId(), securityRole.getRoleId()));
            } else if (DATA_DEPT_SCOPE.equals(dataScope)) {
                SqlBuilder.append(StrUtil.format(" OR {}.dept_id = '{}' ", deptAlias, securityUser.getDeptId()));
            } else if (DATA_DEPT_AND_CHILD_SCOPE.equals(dataScope)) {
                SqlBuilder.append(StrUtil.format(" OR {}.dept_id IN ( SELECT dept_id FROM platform_dept WHERE deleted != '1' AND status = true AND tenant_id = '{}' AND dept_id = '{}' OR parent_id = '{}' )", deptAlias, securityUser.getTenantId(), securityUser.getDeptId(), securityUser.getDeptId()));
            } else if (DATA_SELF_SCOPE.equals(dataScope)) {
                if (StringUtils.isNotBlank(userAlias)) {
                    SqlBuilder.append(StrUtil.format(" OR {}.user_id = '{}' ", userAlias, securityUser.getUserId()));
                } else {
                    // 数据权限为仅本人且没有userAlias别名不查询任何数据
                    SqlBuilder.append(StrUtil.format(" OR {}.dept_id = 0 ", deptAlias));
                }
            }
            conditions.add(dataScope);
        }
        if (StrUtil.isNotEmpty(SqlBuilder.toString())) {
            return SqlBuilder.substring(4);
        } else {
            return SqlBuilder.toString();
        }
    }

}
